﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;


using System.Configuration;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json.Linq;
using System.Dynamic;
using System.Data.SqlClient;
using System.Data;
using System.IO;


namespace TradeWeb.Controllersu
{
    public class BulletController : Controller
    {
        //查询公司公告列表
        public dynamic GetBulletinList(string strUserCode)
        {
            string jsonResult = string.Empty;
            string strSQL = "Select aa.*,bb.ReadUserCode,bb.ReadUserName,bb.State from (select * from T_Bulletin where isdel=0) aa join (select * from T_bulletinRead where isdel=0) bb ON aa.SerialNum=bb.SerialNum where aa.isDel=0 And bb.ReadUserCode=@ReadUserCode ";
            SqlConnection sqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
            SqlCommand sqlCom;
            SqlParameter sqlParam;
            SqlDataAdapter sqlDA;
            DataSet dsList = new DataSet();
            try
            {
                sqlCom = new SqlCommand(strSQL, sqlConn);
                sqlParam = new SqlParameter("@ReadUserCode", SqlDbType.VarChar, 50);
                sqlParam.Value = strUserCode;
                sqlCom.Parameters.Add(sqlParam);

                sqlConn.Open();
                sqlDA = new SqlDataAdapter(sqlCom);
                sqlDA.Fill(dsList, "T_Bulletin");
                sqlConn.Close();

                dsList.Tables[0].DefaultView.Sort = "dates desc";
                List<dynamic> varList = new List<dynamic>();
                foreach (DataRowView drv in dsList.Tables[0].DefaultView)
                {
                    var item = Trade.DBUtility.ExtendObject.AutoMap(drv.Row);
                    varList.Add(item);
                }
                jsonResult = "{\"success\":true  ,\"data\":" + JsonConvert.SerializeObject(varList) + "  ,\"message\":\"正常\"}";
                return jsonResult;
            }
            catch (Exception ex)
            {
                jsonResult = "{\"success\":false  ,\"data\":{}  ,\"message\":\""+ex.Message+"\"}";
                return jsonResult;
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
        }
        //更新公司公告阅读状态为“已阅读”；
        public dynamic UpdateBullet(string strSerialNum,string strUserCode)
        {
            string jsonResult = string.Empty;

            SqlConnection sqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
            SqlParameter sqlParam;
            SqlCommand sqlCom;
            try
            {
                string strSqlUpdate = "Update T_BulletinRead set Dates=getDate(),State='已阅读' where isDel=0 And SerialNum=@SerialNum and ReadUserCode=@ReadUserCode";
                sqlCom = new SqlCommand(strSqlUpdate, sqlConn);

                sqlParam = new SqlParameter("@SerialNum", SqlDbType.VarChar, 50);
                sqlParam.Value = strSerialNum;
                sqlCom.Parameters.Add(sqlParam);

                sqlParam = new SqlParameter("@ReadUserCode", SqlDbType.VarChar, 50);
                sqlParam.Value = strUserCode;
                sqlCom.Parameters.Add(sqlParam);

                sqlConn.Open();
                int iCount = sqlCom.ExecuteNonQuery();
                sqlConn.Close();
                jsonResult = "{\"success\":true  ,\"data\":" + "[{\"msg\":\"成功\"}]" + "  ,\"message\":\"正常\"}";
            }
            catch (Exception ex)
            {
                //throw (ex);
                jsonResult = "{\"success\":false  ,\"data\":{}   ,\"message\":\"" + ex.Message + "\"}";
            }
            finally 
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
            return jsonResult;
        }
        //获取公司公告-未读数目
        public dynamic GetBulletinCount(string strUserCode)
        {
            string jsonResult = string.Empty;
            string strSQL = "SELECT count(*) FROM  dbo.T_BulletinRead  WHERE isDel=0 And ReadUserCode='"+strUserCode+"' AND isnull(state,'')<>'已阅读'";
            SqlConnection sqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
            SqlDataAdapter sqlDA;
            DataSet dsList = new DataSet();
            try
            {
                sqlConn.Open();
                sqlDA = new SqlDataAdapter(strSQL, sqlConn);
                sqlDA.Fill(dsList, "T_Bulletin");
                sqlConn.Close();
                int iCount = 0;
                if (dsList != null && dsList.Tables["T_Bulletin"].Rows.Count > 0)
                {
                    iCount =(int)dsList.Tables["T_Bulletin"].Rows[0][0];
                }
                jsonResult = "{\"success\":true  ,\"data\":" + "[{\"Count\":\"" + iCount + "\"}]" + "  ,\"message\":\"正常\"}";
            }
            catch (Exception ex)
            {
                //throw (ex);
                jsonResult = "{\"success\":false  ,\"data\":{}  ,\"message\":\"" + ex.Message + "\"}";
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
            return jsonResult;
        }
        //获取公司公告详细信息
        public string  GetBulletin(string strSerialNum)
        {
            string jsonResult = string.Empty;
            string strSQL = "select aa.* from (select * from T_Bulletin where isdel=0) aa where aa.SerialNum='" + strSerialNum + "'";
            SqlConnection sqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
            SqlDataAdapter sqlDABullet;

            string strSQLFiles = "SELECT aa.serialNum,ISNULL(bb.SerialID,'') AS SerialID,ISNULL(bb.FileNameFtp,'') AS FileNameFtp,'' AS ShortFileName,'' AS ExtFileName,'' AS FileSize, ISNULL(bb.GUID,'') AS GUID "
                +" FROM (SELECT * from T_Bulletin WHERE isdel=0) aa LEFT JOIN  (SELECT * FROM dbo.T_BulletinProduct WHERE isdel=0) bb "
                + " ON ISNULL(aa.SerialNum,'')=ISNULL(bb.SerialNum,'') WHERE aa.SerialNum='" + strSerialNum + "'";
            SqlDataAdapter sqlDAFiles;

            DataSet dsList = new DataSet();
            try
            {
                sqlConn.Open();
                sqlDABullet = new SqlDataAdapter(strSQL, sqlConn);
                sqlDABullet.Fill(dsList,"T_Bulletin");

                sqlDAFiles = new SqlDataAdapter(strSQLFiles, sqlConn);
                sqlDAFiles.Fill(dsList, "T_Files");

                sqlConn.Close();
                for (int i = 0; i < dsList.Tables["T_Bulletin"].Rows.Count; i++)
                {
                    string strBody = dsList.Tables["T_Bulletin"].Rows[i]["Bulletin"].ToString();
                    //每一个换行符，都重新生成一个段落。
                    string[] aryPhrase = strBody.Split(new char[] { '\r', '\n' });
                    string strNewBody = string.Empty;
                    strNewBody = strBody.Replace("\r\n", "<br>").Replace("\r", "<br>").Replace("\n", "<br>");
                    strNewBody = strNewBody.Replace("\0", "&nbsp;&nbsp;").Replace(" ", "&nbsp;&nbsp;");
                    dsList.Tables["T_Bulletin"].DefaultView[i]["Bulletin"] = strNewBody;
                    dsList.Tables["T_Bulletin"].DefaultView[i].EndEdit();
                }
                for (int i = 0; i < dsList.Tables["T_Files"].Rows.Count; i++)
                {
                    string strFileNameFtp = dsList.Tables["T_Files"].Rows[i]["FileNameFtp"].ToString();
                    if ((!string.IsNullOrEmpty(strFileNameFtp)) && strFileNameFtp.Length > 0)
                    {
                        //简短名称strShortFileName
                        int lenFileNameFtp = strFileNameFtp.Length;
                        int iNumCut = strFileNameFtp.LastIndexOf('/') + 1;
                        string strShortFileName = strFileNameFtp.Substring(iNumCut, lenFileNameFtp - iNumCut);
                        dsList.Tables["T_Files"].Rows[i]["ShortFileName"] = strShortFileName;

                        //后缀名
                        int lenShortFileName = strShortFileName.Length;
                        int iNumCut2 = strShortFileName.LastIndexOf('.');
                        string strExtFileName = strShortFileName.Substring(iNumCut2, lenShortFileName-iNumCut2);
                        dsList.Tables["T_Files"].Rows[i]["ExtFileName"] = strExtFileName.ToLower();
                        //大小SIZE
                        dsList.Tables["T_Files"].Rows[i]["FileSize"] = "unKnown";
                    }
                }
                string strDstoJson = JsonConvert.SerializeObject(dsList);
                jsonResult = "{\"success\":true  ,\"data\":" + strDstoJson + "  ,\"message\":\"正常\"}";
            }
            catch (Exception ex)
            {
                jsonResult = "{\"success\":false  ,\"data\":{}   ,\"message\":\"" + ex.Message + "\"}";
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
            return jsonResult;
        }
        //获取公司公告的附件--用户代码+对应的GUID
        public string  DownBulletinFile(string strSerialNum, string strGUID)
        {
            string jsonResult = string.Empty;
            //表中存的名字
            string strFileNameFtp = string.Empty;
            //网络支持的名字
            string strFileName = string.Empty;

            string strSQL = "SELECT isnull(FileNameFtp,'') as FileNameFtp  FROM dbo.T_BulletinProduct WHERE ISDel=0 AND SerialNum='" + strSerialNum + "' AND GUID='" + strGUID + "'";
            SqlConnection sqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
            SqlCommand sqlCom = new SqlCommand(strSQL, sqlConn);
            try
            {
                sqlConn.Open();
                object obj = sqlCom.ExecuteScalar();
                strFileNameFtp = obj == null ? "" : obj.ToString();
                sqlConn.Close();

                //还是放到到异常处理当中。
                if (string.IsNullOrEmpty(strFileNameFtp))
                {
                    strFileNameFtp = "ProductPicture/error.png";
                }
                //中间路径段：创建时的日期+GUID路径；
                string strMiddlePath = string.Empty;
                int iCutLen = strFileNameFtp.LastIndexOf('/') + 1;
                strMiddlePath = strFileNameFtp.Substring(0, iCutLen);
                
                //取到文件名、文件名生成网络路径；
                string fileFullPath = Server.MapPath("/" + "ftp/" + strFileNameFtp);
                strFileName = Path.GetFileName(fileFullPath);

                string strHttpServerAddress = ConfigurationManager.AppSettings["HttpServerAddress"].ToString();
                string strHttpServerPort = ConfigurationManager.AppSettings["HttpServerPort"].ToString();
                string strWebPath = strHttpServerAddress + ":" + strHttpServerPort;

                //中文名称地址栏转换
                strFileName = System.Web.HttpUtility.UrlEncode(strFileName);
                string strWebFilePath = strWebPath + "/ftp/" +strMiddlePath+ strFileName;
                return jsonResult = "{\"success\":true ,\"data\":{\"result\":\"成功\",\"FilePath\":\"" + strWebFilePath + "\"}   ,\"message\":\"\"}";

            }
            catch (Exception ex)
            {
                return jsonResult = "{\"success\":false ,\"data\":{}   ,\"message\":\"" + ex.Message + "\"}";
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
        }
    }
}
